#coding=utf-8
import os
import sqlite3

'''CREATE TABLE COMPANY
       (ID INT PRIMARY KEY     NOT NULL,
       NAME           TEXT    NOT NULL,
       AGE            INT     NOT NULL,
       ADDRESS        CHAR(50),
       SALARY         REAL);'''
'''
select name from sqlite_master where type='table' order by name;
select * from sqlite_master
'''
class myDB:
    def __init__(self,path):
        if not os.path.exists(path):
            conn = sqlite3.connect(path)
            conn.commit()
            conn.close()
        self.conn=sqlite3.connect(path)
    def creat_table(self,tablename,createdict={"mid":"INTEGER PRIMARY KEY  AUTOINCREMENT","word":"TEXT NOT NULL","desc":"TEXT"}):
        result=self.query_r("select name from sqlite_master where type='table' and name=(?)", (tablename,))
        if result:
            print('table(name={}) is existed'.format(tablename))
        else:
            createsec="CREATE TABLE {} (".format(tablename)
            for key in createdict:
                structure=createdict[key]
                createsec+="{} {},".format(key,structure)
            createsec=createsec[:-1]
            createsec+=");"
            self.conn.execute(createsec)
            self.conn.commit()
    def tables(self):
        c=self.conn.cursor()
        c.execute("select name from sqlite_master where type='table'")
        k=c.fetchall()
        return k
    def table_info(self,tablename):
        c=self.conn.cursor()
        c.execute("PRAGMA table_info({})".format(tablename))
        k=c.fetchone()
        return k
    def del_table(self,tablename):
        c=self.conn.cursor()
        c.execute("DROP TABLE {}".format(tablename))
        self.conn.commit()
    def reset_table(self,tablename):
        c=self.conn.cursor()
        deletsec="delete from {};".format(tablename)
        c.execute(deletsec)
        self.conn.commit()
        c.execute("update sqlite_sequence SET seq = 0 where name ='{}';".format(tablename))
        self.conn.commit()
    def insert(self,tablename,sqllist,valuecb):
        c=self.conn.cursor()
        sech="INSERT INTO {}".format(tablename)
        secattr=""
        secvalue=""
        for i in sqllist:
            tailpart="{},".format(i)
            valuet="{},".format('?')
            secattr+=tailpart
            secvalue+=valuet
        secattr=secattr[:-1]
        secvalue=secvalue[:-1]
        seall=sech+" ("+secattr+") VALUES ("+secvalue+")"
        c.execute(seall,valuecb)
        self.conn.commit()
        return c.lastrowid
    def search_one(self,tablename,targetlist,sqllist,valuecb):
        c=self.conn.cursor()
        exesec="SELECT "
        targets=""
        for m in targetlist:
            targets+=(m+",")
        targets=targets[:-1]
        selectsec=exesec+targets+" FROM {}".format(tablename)
        if sqllist:
            wherevalue=""
            for fp in sqllist:
                wherevalue+="{}=(?) AND".format(fp)
            wherevalue=wherevalue[:-4]
            selectsec+=(" where "+wherevalue)
        c.execute(selectsec,valuecb)
        k = c.fetchone()
        return k
    def search_all(self,tablename,targetlist,sqllist,valuecb):
        c=self.conn.cursor()
        exesec="SELECT "
        targets=""
        for m in targetlist:
            targets+=(m+",")
        targets=targets[:-1]
        selectsec=exesec+targets+" FROM {}".format(tablename)
        if sqllist:
            wherevalue=""
            for fp in sqllist:
                wherevalue+="{}=(?) AND".format(fp)
            wherevalue=wherevalue[:-4]
            selectsec+=(" where "+wherevalue)
        c.execute(selectsec,valuecb)
        k = c.fetchall()
        return k
    def update(self,tablename,targetlist,sqllist,valuecb):
        c=self.conn.cursor()
        exesec="UPDATE {} set ".format(tablename)
        if targetlist:
            setvalue=""
            for fp in targetlist:
                setvalue+="{}=(?),".format(fp)
            setvalue=setvalue[:-1]
            exesec+=setvalue
        if sqllist:
            wherevalue=""
            for fp in sqllist:
                wherevalue+="{}=(?) AND".format(fp)
            wherevalue=wherevalue[:-4]
            exesec+=(" where "+wherevalue)
        c.execute(exesec,valuecb)
        self.conn.commit()
    def delete(self,tablename,sqllist,valuecb):
        c=self.conn.cursor()
        selectsec="DELETE FROM {}".format(tablename)
        if sqllist:
            wherevalue=""
            for fp in sqllist:
                wherevalue+="{}=(?) AND".format(fp)
            wherevalue=wherevalue[:-4]
            selectsec+=(" where "+wherevalue)
        c.execute(selectsec,valuecb)
        self.conn.commit()
    def query_r(self,query,valuecp):
        c=self.conn.cursor()
        c.execute(query,valuecp)
        k=c.fetchall()
        return k
    def query_n(self,query,valuecp):
        c=self.conn.cursor()
        c.execute(query,valuecp)
        self.conn.commit()
    def row_query(self,query):
        c=self.conn.cursor()
        c.execute(query)
        k=c.fetchall()
        return k
    def exit(self):
        self.conn.close()